package db;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class Excel2Mysql {
	private final int FLAG_ROW = 0;
	private final int NAME_ROW = 1;
	private final int TYPE_ROW = 2;
	private final int OTHER_ROW = 3;
	private final int START_ROW = 4;
	
	public boolean exec(String fileFullPath, String urlDb, String userDb, String passwordDb)
			throws InvalidFormatException, FileNotFoundException, IOException, SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
		Workbook workbook = WorkbookFactory.create(new FileInputStream(fileFullPath));
		if (workbook == null) {
			return false;
		}
		
		for (int i = 0; i < workbook.getNumberOfSheets(); ++i) {
			Sheet sheet = workbook.getSheetAt(i);
			Row row = sheet.getRow(FLAG_ROW);
			if (row == null) continue;
			Cell cell = row.getCell(0);
			if (cell.getStringCellValue().compareToIgnoreCase("mysql") != 0) {
				continue;
			}
			
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection(urlDb, userDb, passwordDb);
			Statement statement = con.createStatement();
			dropTable(sheet, statement);
			createTable(sheet, statement);
			insert(sheet, statement);
			con.close();
		}
		return true;
	}
	
	private void insert(Sheet sheet, Statement statement) throws SQLException {
		String sql = "insert into " + sheet.getSheetName() + " values";
		for (int i = START_ROW; i <= sheet.getLastRowNum(); ++i) {
			sql += "(";
			Row row = sheet.getRow(i);
			for (int j = 0; j < row.getLastCellNum(); ++j) {
				Cell cell = row.getCell(j);
				if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
					sql += "\"" + cell.getStringCellValue() + "\"";
				} else {
					sql += (int)cell.getNumericCellValue();
				}
				
				if (j < row.getLastCellNum() - 1) {
					sql += ",";
				}
			}
			sql += ")";
			if (i < sheet.getLastRowNum()) sql += ",";
		}
		
		System.out.println(sql);
		statement.execute(sql);
	}

	private void createTable(Sheet sheet, Statement statement) throws SQLException {
		Row nameRow = sheet.getRow(NAME_ROW);
		Row typeRow = sheet.getRow(TYPE_ROW);
		Row otherRow = sheet.getRow(OTHER_ROW);
		
		String sql = "create table if not exists " + sheet.getSheetName() + "(";
		for (int i = nameRow.getFirstCellNum(); i < nameRow.getLastCellNum(); ++i) {
			sql += nameRow.getCell(i).getStringCellValue() + " "
				+ typeRow.getCell(i).getStringCellValue();
			if (otherRow.getCell(i) != null) sql += " " + otherRow.getCell(i).getStringCellValue();
			if (i < nameRow.getLastCellNum() - 1) sql += ",";
		}
		sql += ")";
		
		System.out.println(sql);
		statement.execute(sql);
	}

	private void dropTable(Sheet sheet, Statement statement) throws SQLException {
		String sql = "drop table if exists " + sheet.getSheetName();
		System.out.println(sql);
		statement.execute(sql);
	}

	public static void main(String args[]) throws InvalidFormatException, FileNotFoundException, IOException, SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException{
		Excel2Mysql excel2mysql = new Excel2Mysql();
		excel2mysql.exec("", "", "", "");
	}
}
